﻿using System;
using System.IO;
using System.Data;
using System.Drawing;
using System.Threading;
using System.Windows.Forms;
using System.Data.SqlClient;
using SYDZ.WMS.DBUtility;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using ElectronicWMSA.Comm.Forms.Message;
using AForge;
using NPOI.SS.Util;

namespace ElectronicWMSA.Warehouse
{
    public partial class Frm_JHKWM : Form
    {
        private string strWhere = "";
        public Frm_JHKWM()
        {
            InitializeComponent();
        }
        private void btnUserSearch_Click(object sender, EventArgs e)
        {
            string _BH_Data = text_TK.Text.Trim();
            if (_BH_Data != "" && _BH_Data.Length == 8 && Comm.ClassFiles.ValidationRegex.ValidteData(_BH_Data))
            {
                strWhere = "  BH_Date ='" + text_TK.Text.Trim() + "'";

                string sql1 = " SELECT SUM(CONVERT(int,[BH_NUM])) AS BH_Num FROM [dbo].[T_BH]  WHERE  [BH_Date]= '" + _BH_Data + "' ";
                var vr1 = DbHelperSQL.GetSingle(sql1);
                if (vr1 != null) { textBox1.Text = vr1.ToString(); }

                string sql2 = "  SELECT COUNT(NID) AS CH_Num FROM dbo.T_PickStockPrepare      WHERE BK08='" + _BH_Data + "'   ";
                var vr2 = DbHelperSQL.GetSingle(sql2);
                if (vr2 != null) { textBox2.Text = vr2.ToString(); }

                _dt_Info = new DataTable();
                WaitingBoxFrm wf = new WaitingBoxFrm((obj, args) => { Thread.Sleep(100); GetData(); }, 30); wf.ShowDialog(this);

                if (_dt_Info.Rows.Count > 0)
                {
                    this.dataGridViewUserList.AutoGenerateColumns = false;
                    this.dataGridViewUserList.DataSource = _dt_Info;
                }
            }
            else
            {
                MessageBox.Show("输入备货日期有误，请确认！");
            }
            if (dataGridViewUserList.Rows.Count == 0)
            {
                MessageBox.Show("未查询到数据，请确认！");
            }
        }

        DataTable _dt_Info = new DataTable();
        private void GetData()
        {
            IDataParameter[] parameters = new IDataParameter[]
                                        {
                                            new SqlParameter("@Tables",SqlDbType.NVarChar)
                                       };
            parameters[0].Direction = ParameterDirection.Input;
            parameters[0].Value = strWhere;
            _dt_Info = DbHelperSQL.RunProcedure("Pro_BH", parameters, "dtt").Tables[0];
        }

        // 导出
        private void btn_exct_Click(object sender, EventArgs e)
        {
            if (_dt_Info.Rows.Count > 0)
            {
                GetNopiExcel(_dt_Info);
            }
            else
            {
                MessageBox.Show("请先查询数据");
            }
        }

        /// <summary>
        /// 列转换数值类型
        /// </summary>
        private DataTable UpdateDataTable(DataTable argDataTable)
        {
            DataTable dtResult = new DataTable();
            // 克隆表结构
            dtResult = argDataTable.Clone();
            foreach (DataColumn col in dtResult.Columns)
            {
                if (col.ColumnName == "CH")
                {
                    col.DataType = typeof(String);
                }
            }
            foreach (DataRow row in argDataTable.Rows)
            {
                DataRow rowNew = dtResult.NewRow();
                rowNew["machine"] = row["machine"];
                rowNew["KC1"] = row["KC1"].ToString();
                rowNew["CH"] = row["CH"].ToString();
                rowNew["欠数"] = row["欠数"].ToString();
                rowNew["Posiont1"] = row["Posiont1"];
                rowNew["time1"] = row["time1"];
                dtResult.Rows.Add(rowNew);
            }
            return dtResult;
        }
        public void GetNopiExcel(DataTable _dtData)
        {
            string _Var1 = "";
            string _Var2_CH = "";
            string _Var3_QS = "";

            for (int i = 0; i < _dtData.Rows.Count; i++)
            {
                if (_Var1 == _dtData.Rows[i]["machine"].ToString())
                {
                    _dtData.Rows[i]["CH"] = "0";
                    _dtData.Rows[i]["欠数"] = "0";
                }
                else
                {
                    _Var1 = _dtData.Rows[i]["machine"].ToString();

                    _Var2_CH = _dtData.Rows[i]["CH"].ToString();
                    _Var3_QS = _dtData.Rows[i]["欠数"].ToString();

                    _dtData.Rows[i]["CH"] = _Var2_CH;
                    _dtData.Rows[i]["欠数"] = _Var3_QS;
                }
            }

            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            saveFileDialog1.Filter = "xls(*.xls)|*.xls";
            saveFileDialog1.FilterIndex = 0;
            saveFileDialog1.RestoreDirectory = true;
            saveFileDialog1.CreatePrompt = true;
            saveFileDialog1.Title = "导出xls文件到 ";
            saveFileDialog1.ShowDialog();

            string sSaveFullPath = saveFileDialog1.FileName;
            if (sSaveFullPath != "")
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                try
                {
                    ISheet sheet = workbook.CreateSheet("Sheet1");
                    ICellStyle HeadercellStyle = workbook.CreateCellStyle();
                    HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

                    IFont headerfont = workbook.CreateFont();  
                    headerfont.Boldweight = (short)FontBoldWeight.Bold;
                    headerfont.FontHeightInPoints = 15;
                    HeadercellStyle.SetFont(headerfont);

                    ICellStyle cellStyle1 = workbook.CreateCellStyle();
                    cellStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    cellStyle1.VerticalAlignment = VerticalAlignment.Center;
                    cellStyle1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                    cellStyle1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                    cellStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                    cellStyle1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

                    int icolIndex = 0;
                    IRow headerRow = sheet.CreateRow(0);
                    string[] strArrName = { "机 型", "库 位", "库存数量", "出库数量", "欠 数", "日 期" };
                    foreach (string item in strArrName)
                    {
                        ICell cell = headerRow.CreateCell(icolIndex);
                        cell.SetCellValue(item.ToString());
                        cell.CellStyle = HeadercellStyle;
                        icolIndex++;
                    }


                    int iRowIndex = 0;
                    int iCellIndex = 0;

                    for (int i = 0; i < _dtData.Rows.Count; i++)
                    {
                        iRowIndex++;
                        iCellIndex = 0;
                        IRow DataRow = sheet.CreateRow(iRowIndex);
                        for (int j = 0; j < _dtData.Columns.Count; j++)
                        {
                            ICell cell = DataRow.CreateCell(iCellIndex);
                            if (_dtData.Rows[i][j].ToString() == "" || _dtData.Rows[i][j].ToString() == "0")
                            {
                                cell.SetCellValue("");
                            }
                            else
                            {
                                if (j == 2 || j == 3 || j == 4)
                                {
                                    cell.SetCellType(CellType.Numeric);
                                    cell.SetCellValue(Int32.Parse(_dtData.Rows[i][j].ToString()));
                                }
                                else
                                {
                                    cell.SetCellValue(_dtData.Rows[i][j].ToString());
                                }
                            }
                            cell.CellStyle = cellStyle1;
                            iCellIndex++;
                        }
                    }
                    int RowIndex = 0;
                    int StartRowIndex = 0;
                    int EndRowIndex = 0;
                    for (int i = 0; i < _dtData.Rows.Count; i++)
                    {
                        RowIndex++;
                        if (_Var1 == _dtData.Rows[i]["machine"].ToString())
                        {
                            if (StartRowIndex == 0)
                            {
                                StartRowIndex = RowIndex - 1;
                            }
                        }
                        else
                        {
                            _Var1 = _dtData.Rows[i]["machine"].ToString();
                            EndRowIndex = RowIndex-1;
                            if (StartRowIndex > 0 && EndRowIndex > 0)
                            {
                                sheet.AddMergedRegion(new CellRangeAddress(StartRowIndex, EndRowIndex, 0, 0));
                                sheet.AddMergedRegion(new CellRangeAddress(StartRowIndex, EndRowIndex, 3, 3));
                                sheet.AddMergedRegion(new CellRangeAddress(StartRowIndex, EndRowIndex, 4, 4));
                                StartRowIndex = 0;
                                EndRowIndex = 0;
                            }
                        }
                    }
                    #region  
                    //string _Var1 = "";
                    //string _Var2 = "";
                    //string _Var3 = "";
                    //string _Var4 = "";
                    //string _Values1 = "";
                    //for (int i = 0; i < _dtData.Rows.Count; i++)
                    //{
                    //    if (_Values1 == _dtData.Rows[i]["machine"].ToString())
                    //    {
                    //        _Var1 += "\r\n" + _dtData.Rows[i]["machine"].ToString();
                    //        _Var2 += "\r\n" + _dtData.Rows[i]["Posiont1"].ToString();
                    //        _Var3 += "\r\n" + _dtData.Rows[i]["KC1"].ToString();
                    //        _Var4 += "\r\n" + _dtData.Rows[i]["time1"].ToString();

                    //        IRow DataRow = sheet.CreateRow(iRowIndex);
                    //        for (int j = 0; j < _dtData.Columns.Count; j++)
                    //        {
                    //            ICell cell = DataRow.CreateCell(iCellIndex);

                    //            if (_dtData.Rows[i][j].ToString() == _Values1)
                    //            {
                    //                cell.SetCellValue(_Var1.ToString());
                    //            }
                    //            else if (j == 1)
                    //            {
                    //                cell.SetCellValue(_Var2.ToString());
                    //            }
                    //            else if (j == 2)
                    //            {
                    //                cell.SetCellValue(_Var3.ToString());
                    //            }
                    //            else if (j == 5)
                    //            {
                    //                cell.SetCellValue(_Var4.ToString());
                    //            }
                    //            else
                    //            {
                    //                cell.SetCellValue(_dtData.Rows[i][j].ToString());
                    //            }

                    //            iCellIndex++;
                    //        }
                    //        iCellIndex = 0;
                    //    }
                    //    else
                    //    {
                    //        _Var1 = "";
                    //        _Var2 = "";
                    //        _Var3 = "";
                    //        _Var4 = "";

                    //        _Values1 = "";

                    //        iRowIndex++;
                    //        IRow DataRow = sheet.CreateRow(iRowIndex);
                    //        for (int j = 0; j < _dtData.Columns.Count; j++)
                    //        {
                    //            _Values1 = _dtData.Rows[i]["machine"].ToString();
                    //            ICell cell = DataRow.CreateCell(iCellIndex);

                    //            cell.SetCellValue(_dtData.Rows[i][j].ToString());

                    //            iCellIndex++;
                    //        }
                    //        iCellIndex = 0;
                    //    }
                    //}
                    #endregion

                    for (int i = 0; i < icolIndex; i++)
                    {
                        sheet.AutoSizeColumn(i);
                    }

                    FileStream file = new FileStream(sSaveFullPath, FileMode.OpenOrCreate);
                    workbook.Write(file);
                    file.Flush();
                    file.Close();
                    MessageBox.Show("导出成功!");
                }
                catch (Exception)
                {
                    MessageBox.Show("导出失败!");
                }
                finally
                {
                    workbook = null;
                }
            }
        }

        private void dataGridViewUserList_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
            SolidBrush b = new SolidBrush(dataGridViewUserList.RowHeadersDefaultCellStyle.ForeColor);
            e.Graphics.DrawString((e.RowIndex + 1).ToString(System.Globalization.CultureInfo.CurrentUICulture), dataGridViewUserList.DefaultCellStyle.Font, b, e.RowBounds.Location.X + 20, e.RowBounds.Location.Y + 4);
        }

        private void linkLabelClose_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            Main._Main.CloseTabPages();
        }
        private void linkLabellinkLabelCloseAll_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            Main._Main.CloseAllTabPages();
        }
        private void linkLabelCloseElse_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            Main._Main.CloseTabPageElse();
        }
    }
}
